﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Globalization;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_MasterPlan : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection myConnection2;

        private bool calculated;
        //private bool loadKoseiUnion;

        private int state;
        
        public panel_MasterPlan()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.myConnection2 = new SqlConnection(mySetting.SQLConnectionString);

            this.calculated = false;
            //this.loadKoseiUnion = false;
            this.state = 0;

            this.buttonBack.Visible = false;
            this.buttonDelayOrder.Visible = false;
            this.buttonFinalOrder.Visible = false;
            this.buttonExport.Visible = false;
            this.buttonYoyaku.Visible = false;

            LoadData();
            
            this.MasterPlan.PopupMenuShowing += MasterPlan_PopupMenuShowing;
            this.masterContext.ItemClicked += masterPlanContext_ItemClicked;
        }


        void masterPlanContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.masterContext.Hide();            
            switch (e.ClickedItem.Name)
            {
                case "importSelection":
                    importDatabase();
                    break;
                case "deleteSelection":
                    deleteRecord();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void MasterPlan_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.masterContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT * FROM [241_Plan]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridMasterPlan.DataSource = sourceDataSet.Tables[0];
                getUpdateInfo();
            }
            else
            {
                MessageBox.Show("There is no data for Master Plan");
                this.buttonForecast.Visible = false;
                DataTable newTable = new DataTable();
                newTable.Columns.Add("PlanNo", typeof(string));
                newTable.Columns.Add("RobotNo", typeof(string));
                newTable.Columns.Add("ExportDate", typeof(DateTime));
                newTable.Columns.Add("PartDate", typeof(DateTime));
                newTable.Columns.Add("RobotQuantity", typeof(int));
                newTable.Columns.Add("PlanTypeID", typeof(string));
                gridMasterPlan.DataSource = newTable;
            }
            MasterPlan.Columns[0].Caption = "Plan No";
            MasterPlan.Columns[1].Caption = "Robot No";
            MasterPlan.Columns[2].Caption = "Export Date";
            MasterPlan.Columns[3].Caption = "Part Date";
            MasterPlan.Columns[4].Caption = "Robot Quantity";
            MasterPlan.Columns[5].Caption = "Plan Type";
            MasterPlan.BestFitColumns();

            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT TOP 1 * FROM [241_LongLeadTime]";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    if (string.IsNullOrEmpty(dataReader["LongLeadTime"].ToString()))
                    {
                        this.LongLeadTime.Text = "30";
                    }
                    else
                    {
                        this.LongLeadTime.Text = dataReader["LongLeadTime"].ToString();
                    }

                    if (string.IsNullOrEmpty(dataReader["LongLeadTimeSP"].ToString()))
                    {
                        this.LongLeadTimeSP.Text = "30";
                    }
                    else
                    {
                        this.LongLeadTimeSP.Text = dataReader["LongLeadTimeSP"].ToString();
                    }
                }
                else
                {
                    this.LongLeadTime.Text = "30";
                    this.LongLeadTimeSP.Text = "30";
                }
                dataReader.Close();
                sql_cmd.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                this.LongLeadTime.Text = "30";
            }
        }

        /////////////////////////////////////
        /// Get Update Information
        /////////////////////////////////////
        private void getUpdateInfo()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT LastUpdate FROM [222_UpdateHistory] WHERE ID=19";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    this.updateDate.Text = dataReader["LastUpdate"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get update information");
            }
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            importDatabase();
        }

        ////////////////////////////////////
        /// Update database From Excel
        ////////////////////////////////////

        private void importDatabase()
        {
            OleDbDataAdapter adp;
            DataTable excel_data = new DataTable();
            
            bool have_data = false;

            importFile.Filter = "Excel 2007-2010 (.xlsx)|*.xlsx|Excel 97-2003 (.xls)|*.xls";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {
                // Delete all current data in Master Plan
                deleteAll();

                // Import new data from Excel
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("Select [PlanNo], [RobotNo], [ExportDate], [PartDate],"
                        + "[RobotQuantity], [PlanType] from [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    adp = new OleDbDataAdapter(cmd);
                    adp.Fill(excel_data);
                    have_data = true;
                    excelConnection.Close();                    
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    have_data = false;
                    MessageBox.Show("Get data error: " + ex.Message);
                    //MessageBox.Show("Cannot import data from file. Please check your file");
                }
            }            

            if (have_data)
            {
                DataSet maker = new DataSet();
                string sql = null;
                SqlDataAdapter adapter;
                DataTable masterplan_local;
                try
                {
                    //Get Data From Local Database
                    myConnection.Open();
                    sql = "SELECT * FROM [241_Plan]";
                    SqlCommand cmd = new SqlCommand(sql, myConnection);
                    adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(maker, "241_Plan");
                    masterplan_local = maker.Tables["241_Plan"];
                    
                    foreach (DataRow excel_row in excel_data.Rows)
                    {
                        if (!string.IsNullOrEmpty(excel_row["ExportDate"].ToString().Trim()) && (!string.IsNullOrEmpty(excel_row["PartDate"].ToString().Trim())))
                        {                            
                            DataRow new_row = masterplan_local.NewRow();
                            new_row["PlanNo"] = excel_row["PlanNo"].ToString();
                            new_row["RobotNo"] = excel_row["RobotNo"].ToString();
                            /*
                            double exd = 0;
                            double.TryParse(excel_row["ExportDate"].ToString().Trim(), out exd);
                            new_row["ExportDate"] = DateTime.FromOADate(exd).Date; */
                            new_row["ExportDate"] = excel_row["ExportDate"].ToString().Trim();
                            /*
                            double pad = 0;
                            double.TryParse(excel_row["PartDate"].ToString().Trim(), out pad);
                            new_row["PartDate"] = DateTime.FromOADate(pad).Date;*/

                            new_row["PartDate"] = excel_row["PartDate"].ToString().Trim();
                            new_row["RobotQuantity"] = Int32.Parse(excel_row["RobotQuantity"].ToString());
                            new_row["PlanTypeID"] = excel_row["PlanType"].ToString();
                            masterplan_local.Rows.Add(new_row);
                        }
                    }
                    
                    SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                    //Update to Local Database
                    adapter.Update(maker, "241_Plan");

                    adapter.Dispose();
                    cmd.Dispose();
                    myConnection.Close();

                    //Update History
                    myConnection.Open();
                    sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=19";
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();

                    LoadData();

                    MessageBox.Show("Database has been updated successfull.");

                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show("Import Error: " + ex.Message);
                }
            }
        }

        private void deleteRecord()
        {
            if (MessageBox.Show("Are you really to delete selected records now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {                
                int[] selected_row = MasterPlan.GetSelectedRows();
                myConnection.Open();
                int k = 0;
                foreach (int i in selected_row)
                {
                    bool exist = false;
                    try
                    {
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT * FROM [241_Plan] WHERE"
                            + " PlanNo='" + MasterPlan.GetRowCellValue(i, "PlanNo").ToString() + "'";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            exist = true;
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                    }
                    catch
                    {
                        exist = false;
                    }
                    if (exist)
                    {
                        try
                        {
                            string sql = "DELETE FROM [241_Plan] "
                                            + "WHERE PlanNo='" + MasterPlan.GetRowCellValue(i, "PlanNo").ToString() + "'";
                            SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                            cmd_sql.ExecuteNonQuery();
                            cmd_sql.Dispose();
                            k++;
                        }
                        catch
                        {

                        }
                    }
                }
                myConnection.Close();
                MessageBox.Show(k + " recoreds have been deleted.");
                LoadData();
            }
        }

        private void deleteAll()
        {
            try
            {
                myConnection.Open();
                string sql = "DELETE [241_Plan]";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
                LoadData();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
            }
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteRecord();
        }

        private void buttonForecast_Click(object sender, EventArgs e)
        {
            if (!calculated)
            {
                ForecastCalculate();
            }
            else
            {
                if (MessageBox.Show("Do you really want to calculate new data?", "Confirm re-calculate", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    ForecastCalculate();
                }
            }

            ShowFinalOrder();
        }

        ///////////////////////////////////////////
        /// Calculate Forecast
        ///////////////////////////////////////////
        private void ForecastCalculate()
        {
            bool pass = true;
            int leadtime = 0;
            int leadtimesp = 0;
            if (string.IsNullOrEmpty(this.LongLeadTime.Text.Trim()))
            {
                MessageBox.Show("Please enter Long Lead Time");
                pass = false;
            }
            else
            {
                if (!Int32.TryParse(this.LongLeadTime.Text.Trim(), out leadtime))
                {
                    MessageBox.Show("Invalid Long Lead Time");
                    pass = false;
                }
                else
                {
                    if (leadtime <= 0)
                    {
                        MessageBox.Show("Invalid Long Lead Time");
                        pass = false;
                    }
                }
            }

            if (pass)
            {
                if (string.IsNullOrEmpty(this.LongLeadTimeSP.Text.Trim()))
                {
                    MessageBox.Show("Please enter Long Lead Time SP");
                    pass = false;
                }
                else
                {
                    if (!Int32.TryParse(this.LongLeadTimeSP.Text.Trim(), out leadtimesp))
                    {
                        MessageBox.Show("Invalid Long Lead Time SP");
                        pass = false;
                    }
                    else
                    {
                        if (leadtimesp <= 0)
                        {
                            MessageBox.Show("Invalid Long Lead Time");
                            pass = false;
                        }
                    }
                }
            }

            if (pass)
            {
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "DELETE FROM [241_LongLeadTime]";
                    sql_cmd.ExecuteNonQuery();
                    sql_cmd.Dispose();

                    sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "INSERT INTO [241_LongLeadTime] (LongLeadTime, LongLeadTimeSP)"
                        + " VALUES (" + leadtime + ", " + leadtimesp + ")";
                    sql_cmd.ExecuteNonQuery();
                    sql_cmd.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    pass = false;
                }
            }
            if (pass)
            {
                if (MessageBox.Show("Do you really want to update Kosei Table?", "Confirm Update Kosei", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    loadKoseiUnionAS400();
                }

                string error = null;

                // Check if there are some plan to calculate            
                if (this.MasterPlan.RowCount <= 0)
                {
                    MessageBox.Show("There is no plan to calculate Forecast");
                }
                else
                {
                    // Check structure                
                    SqlCommand sql_cmd;
                    SqlDataReader dataReader;
                    try
                    {
                        myConnection.Open();
                        sql_cmd = myConnection.CreateCommand();
                        sql_cmd.CommandText = "SELECT COUNT (*) FROM VNoKoseiCheck";
                        dataReader = sql_cmd.ExecuteReader();
                        if (dataReader.Read())
                        {
                            if (dataReader[0] != DBNull.Value)
                            {
                                if (Int32.Parse(dataReader[0].ToString()) > 0)
                                {
                                    error = "There are some Robot has no structure";
                                }
                            }
                        }
                        dataReader.Close();
                        sql_cmd.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        error = ex.Message;
                    }

                    // Clear old structure
                    if (string.IsNullOrEmpty(error))
                    {
                        try
                        {
                            myConnection.Open();
                            sql_cmd = myConnection.CreateCommand();
                            sql_cmd.CommandText = "DELETE [241_PlanStructure]";
                            sql_cmd.ExecuteNonQuery();
                            sql_cmd.Dispose();

                            sql_cmd = myConnection.CreateCommand();
                            sql_cmd.CommandText = "DELETE [241_Yoyaku]";
                            sql_cmd.ExecuteNonQuery();
                            sql_cmd.Dispose();

                            sql_cmd = myConnection.CreateCommand();
                            sql_cmd.CommandText = "DELETE [241_OrderMOQ]";
                            sql_cmd.ExecuteNonQuery();
                            sql_cmd.Dispose();

                            myConnection.Close();
                        }
                        catch (Exception ex)
                        {
                            if (myConnection.State == ConnectionState.Open)
                            {
                                myConnection.Close();
                            }
                            error = ex.Message;
                        }
                    }

                    // Insert calculate new structure
                    if (string.IsNullOrEmpty(error))
                    {
                        try
                        {
                            myConnection2.Open();
                            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                            int totalplan = this.MasterPlan.RowCount;
                            for (int i = 0; i < this.MasterPlan.RowCount; i++)
                            {
                                ModuleWaitDialog.SetWaitDialogCaption("Calculating structure " + (i + 1).ToString() + " of " + totalplan + "...");

                                DataRow plan_row = this.MasterPlan.GetDataRow(i);
                                DataTable temp_table = CalculateStructure(plan_row["RobotNo"].ToString(), this.getMType.Checked);
                                if (temp_table.Rows.Count > 0)
                                {
                                    foreach (DataRow temp_row in temp_table.Rows)
                                    {
                                        sql_cmd = myConnection2.CreateCommand();
                                        sql_cmd.CommandText = "INSERT INTO [241_PlanStructure]"
                                            + " (PlanNo, RobotNo, ExportDate, PartDate, RobotQuantity, PartNo, KoseiQuantity, PartQuantity, PlanType)"
                                            + " VALUES ('" + plan_row["PlanNo"].ToString() + "', '" + plan_row["RobotNo"].ToString() + "',"
                                            + " @exportdate, @partdate, " + float.Parse(plan_row["RobotQuantity"].ToString()) + ","
                                            + " '" + temp_row["PartNo"].ToString() + "'," + float.Parse(temp_row["Quantity"].ToString()) + ","
                                            + " " + float.Parse(plan_row["RobotQuantity"].ToString()) * float.Parse(temp_row["Quantity"].ToString()) + ","
                                            + " '" + plan_row["PlanTypeID"].ToString() + "')";
                                        DateTime exportdate = new DateTime();
                                        DateTime partdate = new DateTime();
                                        exportdate = DateTime.Parse(plan_row["ExportDate"].ToString());
                                        partdate = DateTime.Parse(plan_row["PartDate"].ToString());
                                        sql_cmd.Parameters.Add("@exportdate", SqlDbType.DateTime).Value = exportdate.Date;
                                        sql_cmd.Parameters.Add("@partdate", SqlDbType.DateTime).Value = partdate.Date;
                                        sql_cmd.ExecuteNonQuery();
                                        sql_cmd.Dispose();
                                    }
                                }
                            }
                            ModuleWaitDialog.CloseWaitDialog();
                            myConnection2.Close();
                        }
                        catch (Exception ex)
                        {
                            if (myConnection2.State == ConnectionState.Open)
                            {
                                myConnection2.Close();
                            }
                            ModuleWaitDialog.CloseWaitDialog();
                            error = ex.Message;
                        }

                        // Create FreeZaiko & FreeOrdering

                        ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                        ModuleWaitDialog.SetWaitDialogCaption("Updating FreeZaiko and FreeOrdering...");
                        try
                        {
                            myConnection.Open();
                            SqlCommand cmd_sql = myConnection.CreateCommand();

                            // Clear current FreeZaiko
                            cmd_sql.CommandText = "DELETE [241_FreeZaiko]";
                            cmd_sql.ExecuteNonQuery();
                            cmd_sql.Dispose();

                            // Insert new FreeZaiko
                            cmd_sql = myConnection.CreateCommand();
                            cmd_sql.CommandText = "INSERT INTO [241_FreeZaiko] (OrderNo, PartNo, Quantity, FreeZaikoDate, FreeZaikoDate2)"
                                + " SELECT ('E' + RIGHT('000000' + convert(varchar(100), ROW_NUMBER() OVER(ORDER BY PARTNO)), 6)) AS ORDERNO,"
                                + " PARTNO, QUANTITY, CONVERT (VARCHAR(8), GETDATE(), 112), CONVERT (VARCHAR(8), GETDATE(), 112)"
                                + " FROM VFreeZaiko2"
                                + " WHERE (PARTNO IS NOT NULL) AND (PARTNO NOT LIKE '')";
                            cmd_sql.CommandTimeout = 30000;
                            cmd_sql.ExecuteNonQuery();
                            cmd_sql.Dispose();

                            // Clear current FreeOrdering
                            cmd_sql = myConnection.CreateCommand();
                            cmd_sql.CommandText = "DELETE [241_FreeOrdering]";
                            cmd_sql.ExecuteNonQuery();
                            cmd_sql.Dispose();

                            // Insert new FreeOrdering
                            cmd_sql = myConnection.CreateCommand();
                            cmd_sql.CommandText = "INSERT INTO [241_FreeOrdering] (OrderNo, PartNo, Quantity, RRCDeadline,"
                                + " ArrivalDeadline, TotalQuantity, Note)"
                                + " SELECT ORDERNO, PARTNO, QUANTITY, RRCDEADLINE, ARRIVALDATE, TOTALQTY, NOTE"
                                + " FROM VFreeOrdering2"
                                + " WHERE (PARTNO IS NOT NULL) AND (PARTNO NOT LIKE '')";
                            cmd_sql.CommandTimeout = 30000;
                            cmd_sql.ExecuteNonQuery();
                            cmd_sql.Dispose();

                            myConnection.Close();
                        }
                        catch (Exception ex)
                        {
                            if (myConnection.State == ConnectionState.Open)
                            {
                                myConnection.Close();
                            }
                            error = ex.Message;
                        }
                        ModuleWaitDialog.CloseWaitDialog();
                    }

                    // Calculate Yoyaku
                    if (string.IsNullOrEmpty(error))
                    {
                        DataTable TotalHaveTable = new DataTable();
                        TotalHaveTable.Columns.Add("OrderNo", typeof(string));
                        TotalHaveTable.Columns.Add("PartNo", typeof(string));
                        TotalHaveTable.Columns.Add("Quantity", typeof(float));
                        TotalHaveTable.Columns.Add("RRCDeadline", typeof(string));
                        TotalHaveTable.Columns.Add("ArrivalDeadline", typeof(string));

                        DataTable TotalNeedTable = new DataTable();
                        TotalNeedTable.Columns.Add("PlanNo", typeof(string));
                        TotalNeedTable.Columns.Add("RobotNo", typeof(string));
                        TotalNeedTable.Columns.Add("RobotQuantity", typeof(float));
                        TotalNeedTable.Columns.Add("PartNo", typeof(string));
                        TotalNeedTable.Columns.Add("Quantity", typeof(float));
                        TotalNeedTable.Columns.Add("ExportDate", typeof(string));
                        TotalNeedTable.Columns.Add("PartDate", typeof(string));

                        DataTable YoyakuTable = new DataTable();
                        YoyakuTable.Columns.Add("PlanNo", typeof(string));
                        YoyakuTable.Columns.Add("RobotNo", typeof(string));
                        YoyakuTable.Columns.Add("RobotQuantity", typeof(string));
                        YoyakuTable.Columns.Add("RobotPartDate", typeof(string));
                        YoyakuTable.Columns.Add("PartNo", typeof(string));
                        YoyakuTable.Columns.Add("NeedQuantity", typeof(string));
                        YoyakuTable.Columns.Add("ImportOrderNo", typeof(string));
                        YoyakuTable.Columns.Add("RRCDeadline", typeof(string));
                        YoyakuTable.Columns.Add("ArrivalDeadline", typeof(string));
                        YoyakuTable.Columns.Add("SupplyQuantity", typeof(string));

                        // Load data to calculate
                        ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                        ModuleWaitDialog.SetWaitDialogCaption("Loading data to calculate Yoyaku...");
                        try
                        {
                            myConnection.Open();
                            // Load Total need
                            sql_cmd = myConnection.CreateCommand();
                            sql_cmd.CommandText = "SELECT PlanNo, RobotNo, RobotQuantity, PartNo, TotalNeedQuantity,"
                                + " CONVERT(VARCHAR(10), ExportDate, 112) AS ExportDate,"
                                + " CONVERT(VARCHAR(10), PartDate, 112) AS PartDate"
                                + " FROM VTotalNeedQuantity";
                            sql_cmd.CommandTimeout = 30000;
                            dataReader = sql_cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                DataRow totalneed_row = TotalNeedTable.NewRow();
                                totalneed_row["PlanNo"] = dataReader["PlanNo"].ToString().Trim();
                                totalneed_row["RobotNo"] = dataReader["PlanNo"].ToString().Trim();
                                float robotqty = 0;
                                float.TryParse(dataReader["RobotQuantity"].ToString().Trim(), out robotqty);
                                totalneed_row["RobotQuantity"] = robotqty;
                                totalneed_row["PartNo"] = dataReader["PartNo"].ToString().Trim();
                                float partqty = 0;
                                float.TryParse(dataReader["TotalNeedQuantity"].ToString().Trim(), out partqty);
                                totalneed_row["Quantity"] = partqty;
                                totalneed_row["ExportDate"] = dataReader["ExportDate"].ToString().Trim();
                                totalneed_row["PartDate"] = dataReader["PartDate"].ToString().Trim();
                                TotalNeedTable.Rows.Add(totalneed_row);
                            }
                            dataReader.Close();
                            sql_cmd.Dispose();

                            // Load Total Have
                            sql_cmd = myConnection.CreateCommand();
                            sql_cmd.CommandText = "SELECT OrderNo, PartNo, Quantity, RRCDeadline, ArrivalDeadline"
                                + " FROM [VTotalHaveQuantity] WHERE PartNo IN"
                                + " (SELECT PartNo  COLLATE Japanese_Unicode_CI_AI AS PartNo FROM VTotalNeedQuantity)";
                            sql_cmd.CommandTimeout = 30000;
                            dataReader = sql_cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                DataRow totalhave_row = TotalHaveTable.NewRow();
                                totalhave_row["OrderNo"] = dataReader["OrderNo"].ToString().Trim();
                                totalhave_row["PartNo"] = dataReader["PartNo"].ToString().Trim();
                                float haveqty = 0;
                                float.TryParse(dataReader["Quantity"].ToString().Trim(), out haveqty);
                                totalhave_row["Quantity"] = haveqty;
                                totalhave_row["RRCDeadline"] = dataReader["RRCDeadline"].ToString().Trim();
                                totalhave_row["ArrivalDeadline"] = dataReader["ArrivalDeadline"].ToString().Trim();

                                TotalHaveTable.Rows.Add(totalhave_row);
                            }
                            dataReader.Close();
                            sql_cmd.Dispose();

                            myConnection.Close();
                        }
                        catch (Exception ex)
                        {
                            if (myConnection.State == ConnectionState.Open)
                            {
                                myConnection.Close();
                            }
                            error = ex.Message;
                        }
                        ModuleWaitDialog.CloseWaitDialog();

                        // Start calculating...
                        if (string.IsNullOrEmpty(error))
                        {
                            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                            int totalrecords = TotalNeedTable.Rows.Count;
                            int yoyakuindex = 1;
                            foreach (DataRow need_row in TotalNeedTable.Rows)
                            {
                                ModuleWaitDialog.SetWaitDialogCaption("Calculating Yoyaku " + yoyakuindex + " of " + totalrecords + "...");
                                yoyakuindex++;
                                float need_quantity = 0;
                                float yoyaku_quantity = 0;
                                float.TryParse(need_row["Quantity"].ToString().Trim(), out need_quantity);
                                if (need_quantity > 0)
                                {
                                    DataRow[] have_rows = TotalHaveTable.Select("PartNo='" + need_row["PartNo"].ToString().Trim() + "'"
                                        + " AND Quantity > 0");
                                    if (have_rows.Length <= 0)
                                    {
                                        yoyaku_quantity = need_quantity;
                                        need_quantity = 0;

                                        // Add to Yoyaku with no supply
                                        DataRow yoyaku_row = YoyakuTable.NewRow();
                                        yoyaku_row["PlanNo"] = need_row["PlanNo"].ToString().Trim();
                                        yoyaku_row["RobotNo"] = need_row["RobotNo"].ToString().Trim();
                                        float rbqty = 0;
                                        float.TryParse(need_row["RobotQuantity"].ToString().Trim(), out rbqty);
                                        yoyaku_row["RobotQuantity"] = rbqty;
                                        yoyaku_row["RobotPartDate"] = need_row["PartDate"].ToString().Trim();
                                        yoyaku_row["PartNo"] = need_row["PartNo"].ToString().Trim();
                                        yoyaku_row["NeedQuantity"] = yoyaku_quantity;
                                        yoyaku_row["ImportOrderNo"] = "";
                                        yoyaku_row["RRCDeadline"] = "";
                                        yoyaku_row["ArrivalDeadline"] = "";
                                        yoyaku_row["SupplyQuantity"] = 0;
                                        YoyakuTable.Rows.Add(yoyaku_row);
                                    }
                                    else
                                    {
                                        foreach (DataRow have_row in have_rows)
                                        {
                                            if (need_quantity > 0)
                                            {
                                                float have_quantity = 0;
                                                float.TryParse(have_row["Quantity"].ToString(), out have_quantity);
                                                if (have_quantity >= need_quantity)
                                                {
                                                    yoyaku_quantity = need_quantity;
                                                    have_quantity = have_quantity - need_quantity;
                                                    need_quantity = 0;

                                                    // Add to Yoyaku
                                                    DataRow yoyaku_row = YoyakuTable.NewRow();
                                                    yoyaku_row["PlanNo"] = need_row["PlanNo"].ToString().Trim();
                                                    yoyaku_row["RobotNo"] = need_row["RobotNo"].ToString().Trim();
                                                    float rbqty = 0;
                                                    float.TryParse(need_row["RobotQuantity"].ToString().Trim(), out rbqty);
                                                    yoyaku_row["RobotQuantity"] = rbqty;
                                                    yoyaku_row["RobotPartDate"] = need_row["PartDate"].ToString().Trim();
                                                    yoyaku_row["PartNo"] = need_row["PartNo"].ToString().Trim();
                                                    yoyaku_row["NeedQuantity"] = yoyaku_quantity;
                                                    yoyaku_row["ImportOrderNo"] = have_row["OrderNo"].ToString().Trim();
                                                    yoyaku_row["RRCDeadline"] = have_row["RRCDeadline"].ToString().Trim();
                                                    yoyaku_row["ArrivalDeadline"] = have_row["ArrivalDeadline"].ToString().Trim();
                                                    yoyaku_row["SupplyQuantity"] = yoyaku_quantity;
                                                    YoyakuTable.Rows.Add(yoyaku_row);

                                                    have_row.BeginEdit();
                                                    have_row["Quantity"] = have_quantity;
                                                    have_row.EndEdit();
                                                }
                                                else
                                                {
                                                    yoyaku_quantity = have_quantity;
                                                    need_quantity = need_quantity - have_quantity;
                                                    have_quantity = 0;

                                                    // Add to Yoyaku
                                                    DataRow yoyaku_row = YoyakuTable.NewRow();
                                                    yoyaku_row["PlanNo"] = need_row["PlanNo"].ToString().Trim();
                                                    yoyaku_row["RobotNo"] = need_row["RobotNo"].ToString().Trim();
                                                    float rbqty = 0;
                                                    float.TryParse(need_row["RobotQuantity"].ToString().Trim(), out rbqty);
                                                    yoyaku_row["RobotQuantity"] = rbqty;
                                                    yoyaku_row["RobotPartDate"] = need_row["PartDate"].ToString().Trim();
                                                    yoyaku_row["PartNo"] = need_row["PartNo"].ToString().Trim();
                                                    yoyaku_row["NeedQuantity"] = yoyaku_quantity;
                                                    yoyaku_row["ImportOrderNo"] = have_row["OrderNo"].ToString().Trim();
                                                    yoyaku_row["RRCDeadline"] = have_row["RRCDeadline"].ToString().Trim();
                                                    yoyaku_row["ArrivalDeadline"] = have_row["ArrivalDeadline"].ToString().Trim();
                                                    yoyaku_row["SupplyQuantity"] = yoyaku_quantity;
                                                    YoyakuTable.Rows.Add(yoyaku_row);

                                                    have_row.BeginEdit();
                                                    have_row["Quantity"] = have_quantity;
                                                    have_row.EndEdit();
                                                }
                                            }
                                        }

                                        if (need_quantity > 0)
                                        {
                                            yoyaku_quantity = need_quantity;
                                            need_quantity = 0;

                                            // Add to Yoyaku with no supply
                                            DataRow yoyaku_row = YoyakuTable.NewRow();
                                            yoyaku_row["PlanNo"] = need_row["PlanNo"].ToString().Trim();
                                            yoyaku_row["RobotNo"] = need_row["RobotNo"].ToString().Trim();
                                            float rbqty = 0;
                                            float.TryParse(need_row["RobotQuantity"].ToString().Trim(), out rbqty);
                                            yoyaku_row["RobotQuantity"] = rbqty;
                                            yoyaku_row["RobotPartDate"] = need_row["PartDate"].ToString().Trim();
                                            yoyaku_row["PartNo"] = need_row["PartNo"].ToString().Trim();
                                            yoyaku_row["NeedQuantity"] = yoyaku_quantity;
                                            yoyaku_row["ImportOrderNo"] = "";
                                            yoyaku_row["RRCDeadline"] = "";
                                            yoyaku_row["ArrivalDeadline"] = "";
                                            yoyaku_row["SupplyQuantity"] = 0;
                                            YoyakuTable.Rows.Add(yoyaku_row);
                                        }
                                    }
                                }
                            }
                            ModuleWaitDialog.CloseWaitDialog();

                            // Write Yoyaku to database
                            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                            try
                            {
                                int totalyoyaku = YoyakuTable.Rows.Count;
                                int curr = 1;
                                myConnection.Open();
                                foreach (DataRow ykrow in YoyakuTable.Rows)
                                {
                                    ModuleWaitDialog.SetWaitDialogCaption("Saving Yoyaku " + curr + " of " + totalyoyaku + "...");
                                    curr++;
                                    sql_cmd = myConnection.CreateCommand();
                                    sql_cmd.CommandText = "INSERT INTO [241_Yoyaku] ([PlanNo], [RobotNo], [RobotQuantity],"
                                        + " [RobotPartDate], [PartNo], [NeedQuantity], [ImportOrderNo], [RRCDeadline],"
                                        + " [ArrivalDeadline], [SupplyQuantity]) VALUES"
                                        + " ('" + ykrow["PlanNo"].ToString() + "',"
                                        + " '" + ykrow["RobotNo"].ToString() + "',"
                                        + " " + ykrow["RobotQuantity"].ToString() + ","
                                        + " '" + ykrow["RobotPartDate"].ToString() + "',"
                                        + " '" + ykrow["PartNo"].ToString() + "',"
                                        + " " + ykrow["NeedQuantity"].ToString() + ","
                                        + " '" + ykrow["ImportOrderNo"].ToString() + "',"
                                        + " '" + ykrow["RRCDeadline"].ToString() + "',"
                                        + " '" + ykrow["ArrivalDeadline"].ToString() + "',"
                                        + " " + ykrow["SupplyQuantity"].ToString() + ")";
                                    sql_cmd.ExecuteNonQuery();
                                    sql_cmd.Dispose();
                                }
                                myConnection.Close();
                            }
                            catch (Exception ex)
                            {
                                if (myConnection.State == ConnectionState.Open)
                                {
                                    myConnection.Close();
                                }
                                error = ex.Message;
                            }
                            ModuleWaitDialog.CloseWaitDialog();

                            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                            ModuleWaitDialog.SetWaitDialogCaption("Calculating MOQ...");
                            // Calculate MOQ
                            if (string.IsNullOrEmpty(error))
                            {
                                DataTable OrderMOQTable = new DataTable();
                                OrderMOQTable.Columns.Add("PartNo", typeof(string));
                                OrderMOQTable.Columns.Add("MissMonth", typeof(string));
                                OrderMOQTable.Columns.Add("Quantity", typeof(string));

                                DataTable OrderMOQFromYoyaku = new DataTable();
                                OrderMOQFromYoyaku.Columns.Add("PartNo", typeof(string));
                                OrderMOQFromYoyaku.Columns.Add("MissMonth", typeof(string));
                                OrderMOQFromYoyaku.Columns.Add("TotalMissQuantity", typeof(float));
                                OrderMOQFromYoyaku.Columns.Add("MOQQuantity", typeof(float));
                                OrderMOQFromYoyaku.Columns.Add("MarumeQuantity", typeof(float));

                                // Load data to calculate MOQ
                                try
                                {
                                    myConnection.Open();
                                    sql_cmd = myConnection.CreateCommand();
                                    sql_cmd.CommandText = "SELECT PartNo, MissMonth, SUM(TotalMissQuantity) AS TotalMissQuantity,"
                                        + " MOQuantity, MarumeQuantity"
                                        + " FROM dbo.VOrderQuantityWithMOQ"
                                        + " GROUP BY PartNo, MissMonth, MOQuantity, MarumeQuantity"
                                        + " ORDER BY MissMonth";
                                    dataReader = sql_cmd.ExecuteReader();
                                    while (dataReader.Read())
                                    {
                                        DataRow original_row = OrderMOQFromYoyaku.NewRow();
                                        original_row["PartNo"] = dataReader["PartNo"].ToString().Trim();
                                        original_row["MissMonth"] = dataReader["MissMonth"].ToString().Trim();
                                        float TotalMissQuantity = 0;
                                        float.TryParse(dataReader["TotalMissQuantity"].ToString().Trim(), out TotalMissQuantity);
                                        original_row["TotalMissQuantity"] = TotalMissQuantity;
                                        float MOQQuantity = 0;
                                        float.TryParse(dataReader["MOQuantity"].ToString().Trim(), out MOQQuantity);
                                        original_row["MOQQuantity"] = MOQQuantity;
                                        float MarumeQuantity = 0;
                                        float.TryParse(dataReader["MarumeQuantity"].ToString().Trim(), out MarumeQuantity);
                                        original_row["MarumeQuantity"] = MarumeQuantity;
                                        OrderMOQFromYoyaku.Rows.Add(original_row);
                                    }
                                    dataReader.Close();
                                    sql_cmd.Dispose();
                                    myConnection.Close();
                                }
                                catch (Exception ex)
                                {
                                    if (myConnection.State == ConnectionState.Open)
                                    {
                                        myConnection.Close();
                                    }
                                    error = ex.Message;
                                }

                                // Calculating...
                                float free_qty = 0;
                                foreach (DataRow calculate_row in OrderMOQFromYoyaku.Rows)
                                {
                                    float miss_qty = float.Parse(calculate_row["TotalMissQuantity"].ToString());
                                    if (free_qty >= miss_qty)
                                    {
                                        free_qty = free_qty - miss_qty;
                                    }
                                    else
                                    {
                                        miss_qty = miss_qty - free_qty;
                                        float moq_qty = float.Parse(calculate_row["MOQQuantity"].ToString());
                                        float marume_qty = float.Parse(calculate_row["MarumeQuantity"].ToString());
                                        free_qty = Math.Max(moq_qty, (float)Math.Ceiling(miss_qty / marume_qty) * marume_qty);

                                        DataRow result_row = OrderMOQTable.NewRow();
                                        result_row["PartNo"] = calculate_row["PartNo"].ToString();
                                        result_row["MissMonth"] = calculate_row["MissMonth"].ToString();
                                        result_row["Quantity"] = free_qty;
                                        OrderMOQTable.Rows.Add(result_row);

                                        free_qty = free_qty - miss_qty;
                                    }
                                }

                                // Writing MOQ
                                try
                                {
                                    myConnection.Open();
                                    foreach (DataRow moq_row in OrderMOQTable.Rows)
                                    {
                                        sql_cmd = myConnection.CreateCommand();
                                        sql_cmd.CommandText = "INSERT INTO [241_OrderMOQ] (PartNo, MissMonth, Quantity)"
                                            + " VALUES ('" + moq_row["PartNo"].ToString() + "',"
                                            + " '" + moq_row["MissMonth"].ToString() + "',"
                                            + " " + float.Parse(moq_row["Quantity"].ToString()) + ")";
                                        sql_cmd.ExecuteNonQuery();
                                        sql_cmd.Dispose();
                                    }
                                    myConnection.Close();
                                }
                                catch (Exception ex)
                                {
                                    if (myConnection.State == ConnectionState.Open)
                                    {
                                        myConnection.Close();
                                    }
                                    error = ex.Message;
                                }
                            }
                            ModuleWaitDialog.CloseWaitDialog();
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        MessageBox.Show("Cauculate success. Show result.");
                    }
                    else
                    {
                        MessageBox.Show(error);
                    }
                }
            }
        }

        //////////////////////////////////////////////
        /// Show final order
        /////////////////////////////////////////////
        private void ShowFinalOrder()
        {
            this.state = 1;
            this.labelControl1.Text = "Final Order Quantity";

            DataSet finalOrderSet = new DataSet();            
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT t1.PartNo, t1.Month, t1.Quantity, t1.PartName, t2.MakerName, t3.SupplierName, t1.LeadTime,"
                + " t1.MinQuantity, t1.Marume, t1.PartType"
                + " FROM [VFinalOrderQuantity2] AS t1 LEFT OUTER JOIN"
                + " [222_Maker] AS t2 ON t1.MakerID=t2.MakerID COLLATE Japanese_Unicode_CI_AI LEFT OUTER JOIN"
                + " [222_Supplier] AS t3 ON t1.SupplierID=t3.SupplierID COLLATE Japanese_Unicode_CI_AI";
            finalOrderSet = mySQLManagement.SQL_Query(sql);
            if (finalOrderSet != null)
            {
                this.gridFinalOrder.DataSource = null;
                this.gridFinalOrder.DataSource = finalOrderSet.Tables[0];
            }
            else
            {
                MessageBox.Show("There is no data for Final Order");
                DataTable newTable = new DataTable();                
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("Month", typeof(string));
                newTable.Columns.Add("Quantity", typeof(float));
                newTable.Columns.Add("PartName", typeof(string));
                newTable.Columns.Add("Maker", typeof(string));
                newTable.Columns.Add("Supplier", typeof(string));
                newTable.Columns.Add("LeadTime", typeof(int));
                newTable.Columns.Add("MOQ", typeof(float));
                newTable.Columns.Add("Marume", typeof(float));
                newTable.Columns.Add("PartType", typeof(string));
                this.gridFinalOrder.DataSource = newTable;
            }
            FinalOrder.Columns[0].Caption = "Part No";
            FinalOrder.Columns[1].Caption = "Month";
            FinalOrder.Columns[2].Caption = "Quantity";
            FinalOrder.Columns[3].Caption = "Part Name";
            FinalOrder.Columns[4].Caption = "Maker";
            FinalOrder.Columns[5].Caption = "Supplier";
            FinalOrder.Columns[6].Caption = "Lead Time";
            FinalOrder.Columns[7].Caption = "MOQ";
            FinalOrder.Columns[8].Caption = "Marume";
            FinalOrder.Columns[9].Caption = "Part Type";            

            this.FinalOrder.BestFitColumns();
            this.gridFinalOrder.BringToFront();

            this.buttonBack.Visible = true;
            this.buttonExport.Visible = true;
            this.buttonFinalOrder.Visible = true;
            this.buttonDelayOrder.Visible = true;
            this.buttonYoyaku.Visible = true;

            this.buttonRefresh.Visible = false;
            this.buttonImport.Visible = false;
            this.buttonDelete.Visible = false;
            this.buttonForecast.Visible = false;
            this.buttonViewLast.Visible = false;
            this.getMType.Visible = false;            
        }

        //////////////////////////////////////////////
        /// Show Delay order
        /////////////////////////////////////////////
        private void ShowDelayOrder()
        {
            this.state = 2;
            this.labelControl1.Text = "Delay or Cancel Orders";

            DataSet delayOrderSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            /*
            string sql = "SELECT [OrderNo], [PartNo], [Quantity], [RRCDeadline], [ArrivalDeadline], [TotalQuantity],"
                + " [Note], [TotalSupplyQuantity], [MinRobotPartDate], [MaxRobotPartDate], [Cancel], [Delay],"
                + " [MinQuantity], [DescreaseQuantity]"
                + " FROM [VCancelAndDelay]";*/
            string sql = "SELECT [OrderNo], [PartNo], [TotalQuantity], [Quantity], [RRCDeadline], [ArrivalDeadline],"
                + " [Note], [TotalSupplyQuantity], [MinRobotPartDate], [MaxRobotPartDate], [Cancel], [Delay], [MinQuantity],"
                + " [Marume],"
                + " CASE WHEN [Cancel] IS NULL AND [DecreaseQty] > 0 THEN 'Decrease Quantity' ELSE '' END AS [DescreaseQuantity],"
                + " CASE WHEN [Cancel] IS NOT NULL OR [DecreaseQty] < 0 THEN 0 ELSE [DecreaseQty] END AS [DecreaseQty]"
                + " FROM [VCancelAndDelay]";
            delayOrderSet = mySQLManagement.SQL_Query(sql);
            if (delayOrderSet != null)
            {
                this.gridCancelOrDelay.DataSource = null;
                this.gridCancelOrDelay.DataSource = delayOrderSet.Tables[0];
            }
            else
            {
                MessageBox.Show("There is no data for Delay Order");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("OrderNo", typeof(string));
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("TotalQuantity", typeof(float));
                newTable.Columns.Add("Quantity", typeof(float));
                newTable.Columns.Add("RRCDeadline", typeof(string));
                newTable.Columns.Add("ArrivalDeadline", typeof(string));                
                newTable.Columns.Add("Note", typeof(string));
                newTable.Columns.Add("TotalSupplyQuantity", typeof(float));
                newTable.Columns.Add("MinRobotPartDate", typeof(DateTime));
                newTable.Columns.Add("MaxRobotPartDate", typeof(DateTime));
                newTable.Columns.Add("Cancel", typeof(string));
                newTable.Columns.Add("Delay", typeof(string));
                newTable.Columns.Add("MOQ", typeof(float));
                newTable.Columns.Add("Marume", typeof(float));
                newTable.Columns.Add("DescreaseQuantity", typeof(string));
                newTable.Columns.Add("DescreaseQty", typeof(string));
                this.gridCancelOrDelay.DataSource = newTable;
            }
            CancelOrDelay.Columns[0].Caption = "Order No";
            CancelOrDelay.Columns[1].Caption = "Part No";
            CancelOrDelay.Columns[2].Caption = "Total Q'ty";
            CancelOrDelay.Columns[3].Caption = "Free Q'ty";
            CancelOrDelay.Columns[4].Caption = "RRC Deadline";
            CancelOrDelay.Columns[5].Caption = "Arrival Deadline";            
            CancelOrDelay.Columns[6].Caption = "Note";
            CancelOrDelay.Columns[7].Caption = "Need Q'ty";
            CancelOrDelay.Columns[8].Caption = "Min Robot Part Date";
            CancelOrDelay.Columns[9].Caption = "Max Robot Part Date";
            CancelOrDelay.Columns[10].Caption = "Cancel";
            CancelOrDelay.Columns[11].Caption = "Delay";
            CancelOrDelay.Columns[12].Caption = "MOQ";
            CancelOrDelay.Columns[13].Caption = "Marume";
            CancelOrDelay.Columns[14].Caption = "Descrease";
            CancelOrDelay.Columns[15].Caption = "Descrease Q'ty";

            this.CancelOrDelay.BestFitColumns();
            this.gridCancelOrDelay.BringToFront();

            this.buttonBack.Visible = true;
            this.buttonExport.Visible = true;
            this.buttonFinalOrder.Visible = true;
            this.buttonDelayOrder.Visible = true;
            this.buttonYoyaku.Visible = true;

            this.buttonRefresh.Visible = false;
            this.buttonImport.Visible = false;
            this.buttonDelete.Visible = false;
            this.buttonForecast.Visible = false;
            this.buttonViewLast.Visible = false;
            this.getMType.Visible = false;            
        }

        //////////////////////////////////////////////
        /// Show Yoyaku
        /////////////////////////////////////////////
        private void ShowYoyaku()
        {
            this.state = 3;
            this.labelControl1.Text = "Yoyaku";

            DataSet yoyakuDataSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT PlanNo, RobotNo, RobotQuantity, RobotPartDate, PartNo, NeedQuantity,"
                + " ImportOrderNo, RRCDeadline, ArrivalDeadline, SupplyQuantity"
                + " FROM [241_Yoyaku]";
            yoyakuDataSet = mySQLManagement.SQL_Query(sql);
            if (yoyakuDataSet != null)
            {
                this.gridYoyaku.DataSource = null;
                this.gridYoyaku.DataSource = yoyakuDataSet.Tables[0];
            }
            else
            {
                MessageBox.Show("There is no data for Yoyaku");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("PlanNo", typeof(string));
                newTable.Columns.Add("RobotNo", typeof(string));
                newTable.Columns.Add("RobotQuantity", typeof(float));
                newTable.Columns.Add("RobotPartDate", typeof(DateTime));
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("NeedQuantity", typeof(float));
                newTable.Columns.Add("ImportOrderNo", typeof(string));
                newTable.Columns.Add("RRCDeadline", typeof(DateTime));
                newTable.Columns.Add("ArrivalDeadline", typeof(DateTime));
                newTable.Columns.Add("SupplyQuantity", typeof(float));                
                this.gridYoyaku.DataSource = newTable;
            }
            Yoyaku.Columns[0].Caption = "Plan No";
            Yoyaku.Columns[1].Caption = "Robot No";
            Yoyaku.Columns[2].Caption = "Robot Quantity";
            Yoyaku.Columns[3].Caption = "Robot Part Date";
            Yoyaku.Columns[4].Caption = "Part No";
            Yoyaku.Columns[5].Caption = "Need Quantity";
            Yoyaku.Columns[6].Caption = "Import Order No";
            Yoyaku.Columns[7].Caption = "RRC Deadline";
            Yoyaku.Columns[8].Caption = "Arrival Deadline";
            Yoyaku.Columns[9].Caption = "Supply Quantity";

            this.Yoyaku.BestFitColumns();
            this.gridYoyaku.BringToFront();

            this.buttonBack.Visible = true;
            this.buttonExport.Visible = true;
            this.buttonFinalOrder.Visible = true;
            this.buttonDelayOrder.Visible = true;
            this.buttonYoyaku.Visible = true;

            this.buttonRefresh.Visible = false;
            this.buttonImport.Visible = false;
            this.buttonDelete.Visible = false;
            this.buttonForecast.Visible = false;
            this.buttonViewLast.Visible = false;
            this.getMType.Visible = false;            
        }


        ///////////////////////////////////////////////////
        /// Main Procedure to Calculate Structure for Robot
        ///////////////////////////////////////////////////
        private DataTable CalculateStructure(string robotno, bool getMType)
        {
            DataTable final_result_Table = new DataTable();
            final_result_Table.Columns.Add("RobotNo", typeof(string));
            final_result_Table.Columns.Add("PartNo", typeof(string));
            final_result_Table.Columns.Add("Quantity", typeof(float));
            final_result_Table.Columns.Add("PType", typeof(string));
            final_result_Table.Columns.Add("Final", typeof(int));

            DataTable final_sum_Table = new DataTable();
            final_sum_Table.Columns.Add("RobotNo", typeof(string));
            final_sum_Table.Columns.Add("PartNo", typeof(string));
            final_sum_Table.Columns.Add("Quantity", typeof(float));
            final_sum_Table.Columns.Add("PType", typeof(string));


            // Get Structure From Kosei
            DataTable kosei1 = RobotStructure(robotno, getMType);
            foreach (DataRow kosei1_row in kosei1.Rows)
            {
                DataRow add_row = final_result_Table.NewRow();
                add_row["RobotNo"] = kosei1_row["RobotNo"];
                add_row["PartNo"] = kosei1_row["PartNo"];
                add_row["Quantity"] = kosei1_row["Quantity"];
                add_row["PType"] = kosei1_row["PType"];
                add_row["Final"] = 0;
                final_result_Table.Rows.Add(add_row);
            }

            // SUM part in structure
            foreach (DataRow sum_row in final_result_Table.Rows)
            {
                DataRow[] edit_rows = final_sum_Table.Select("RobotNo='" + sum_row["RobotNo"].ToString() + "'"
                        + " AND PartNo='" + sum_row["PartNo"] + "'");
                if (edit_rows.Length > 0)
                {
                    foreach (DataRow r in edit_rows)
                    {
                        r.BeginEdit();
                        r["Quantity"] = (float)r["Quantity"] + (float)sum_row["Quantity"];
                        r.EndEdit();
                    }
                }
                else
                {
                    DataRow n = final_sum_Table.NewRow();
                    n["RobotNo"] = sum_row["RobotNo"].ToString();
                    n["PartNo"] = sum_row["PartNo"].ToString();
                    n["Quantity"] = (float)sum_row["Quantity"];
                    n["PType"] = sum_row["PType"].ToString();
                    final_sum_Table.Rows.Add(n);
                }
            }
            // return result
            return final_sum_Table;
        }


        //////////////////////////////////////////////
        /// Get Structure From Kosei
        //////////////////////////////////////////////
        private DataTable RobotStructure(string robotno, bool getMType)
        {
            DataTable StructureTable = new DataTable();
            StructureTable.Columns.Add("RobotNo", typeof(string));
            StructureTable.Columns.Add("PartNo", typeof(string));
            StructureTable.Columns.Add("Quantity", typeof(float));            
            StructureTable.Columns.Add("PType", typeof(string));

            DataTable currentTable = new DataTable();
            currentTable.Columns.Add("RobotNo", typeof(string));
            currentTable.Columns.Add("PartNo", typeof(string));
            currentTable.Columns.Add("Quantity", typeof(float));
            currentTable.Columns.Add("PType", typeof(string));
            
            DataTable nextTable = new DataTable();
            nextTable.Columns.Add("RobotNo", typeof(string));
            nextTable.Columns.Add("PartNo", typeof(string));
            nextTable.Columns.Add("Quantity", typeof(float));
            nextTable.Columns.Add("PType", typeof(string));

            
            DataTable resultTable = new DataTable();
            resultTable.Columns.Add("RobotNo", typeof(string));
            resultTable.Columns.Add("PartNo", typeof(string));
            resultTable.Columns.Add("Quantity", typeof(float));
            resultTable.Columns.Add("PType", typeof(string));

            if (!string.IsNullOrEmpty(robotno))
            {
                try
                {                    
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    SqlDataReader dataReader;

                    // Calculate for level 1

                    cmd_sql.CommandText = "SELECT * FROM [241_KoseiUnion] WHERE COMPID='" + robotno + "'";
                    cmd_sql.CommandTimeout = 30000;

                    dataReader = cmd_sql.ExecuteReader();
                    int i = 0;
                    while (dataReader.Read())
                    {
                        i++;
                        DataRow new_row = currentTable.NewRow();
                        new_row["RobotNo"] = dataReader["COMPID"].ToString().Trim();
                        new_row["PartNo"] = dataReader["PARTNO"].ToString().Trim();
                        float quantity = 0;
                        quantity = float.Parse(dataReader["QUANTITY"].ToString().Trim());
                        new_row["Quantity"] = quantity;
                        new_row["PType"] = dataReader["PARTTYPE"].ToString();
                        currentTable.Rows.Add(new_row);
                    }

                    if (i == 0)
                    {
                        DataRow result_row = StructureTable.NewRow();
                        result_row["RobotNo"] = robotno;
                        result_row["PartNo"] = robotno;                        
                        result_row["Quantity"] = 1;
                        result_row["PType"] = "P";
                        StructureTable.Rows.Add(result_row);
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();

                    cmd_sql = myConnection.CreateCommand();
                    // Calculate for all level
                    while (currentTable.Rows.Count > 0)
                    {                           
                        foreach (DataRow row in currentTable.Rows)
                        {
                            try
                            {
                                if (!getMType)
                                {
                                    // Not Get M Type                                    
                                    cmd_sql.CommandText = "SELECT COMPID, PARTNO,"
                                        + " QUANTITY*" + row["Quantity"].ToString().Trim() + " AS QUANTITY, PARTTYPE"
                                        + " FROM [241_KoseiUnion]"
                                        + " WHERE COMPID='" + row["PartNo"].ToString() + "'";
                                    cmd_sql.CommandTimeout = 30000;
                                    dataReader = cmd_sql.ExecuteReader();
                                    int j = 0;
                                    while (dataReader.Read())
                                    {
                                        if (dataReader["COMPID"] != DBNull.Value)
                                        {
                                            j++;
                                            DataRow new_row = nextTable.NewRow();
                                            new_row["RobotNo"] = dataReader["COMPID"].ToString().Trim();
                                            new_row["PartNo"] = dataReader["PARTNO"].ToString().Trim();
                                            float quantity = 0;
                                            quantity = float.Parse(dataReader["QUANTITY"].ToString().Trim());
                                            new_row["Quantity"] = quantity;
                                            new_row["PType"] = dataReader["PARTTYPE"].ToString();
                                            nextTable.Rows.Add(new_row);
                                        }
                                    }
                                    dataReader.Close();
                                    cmd_sql.Dispose();
                                    if (j == 0)
                                    {
                                        DataRow result_row = StructureTable.NewRow();
                                        result_row["RobotNo"] = robotno;
                                        result_row["PartNo"] = row["PartNo"].ToString().Trim();
                                        float quantity = 0;
                                        quantity = float.Parse(row["Quantity"].ToString().Trim());
                                        result_row["Quantity"] = quantity;
                                        result_row["PType"] = row["PType"].ToString();
                                        StructureTable.Rows.Add(result_row);
                                    }
                                }
                                else
                                {
                                    // Get M Type
                                    if (row["PType"].ToString() != "M")
                                    {
                                        // Part is not M type                                        
                                        cmd_sql = myConnection.CreateCommand();
                                        cmd_sql.CommandText = "SELECT COMPID, PARTNO,"
                                        + " QUANTITY*" + row["Quantity"].ToString().Trim() + " AS QUANTITY, PARTTYPE"
                                        + " FROM [241_KoseiUnion]"
                                        + " WHERE COMPID='" + row["PartNo"].ToString() + "'";
                                        cmd_sql.CommandTimeout = 30000;
                                        dataReader = cmd_sql.ExecuteReader();
                                        int k = 0;
                                        while (dataReader.Read())
                                        {
                                            if (dataReader["COMPID"] != DBNull.Value)
                                            {
                                                k++;
                                                DataRow new_row = nextTable.NewRow();
                                                new_row["RobotNo"] = dataReader["COMPID"].ToString().Trim();
                                                new_row["PartNo"] = dataReader["PARTNO"].ToString().Trim();
                                                float quantity = 0;
                                                quantity = float.Parse(dataReader["QUANTITY"].ToString().Trim());
                                                new_row["Quantity"] = quantity;
                                                new_row["PType"] = dataReader["PARTTYPE"].ToString();
                                                nextTable.Rows.Add(new_row);
                                            }
                                        }
                                        dataReader.Close();
                                        cmd_sql.Dispose();

                                        if (k == 0)
                                        {
                                            DataRow result_row = StructureTable.NewRow();
                                            result_row["RobotNo"] = robotno;
                                            result_row["PartNo"] = row["PartNo"].ToString().Trim();
                                            float quantity = 0;
                                            quantity = float.Parse(row["Quantity"].ToString().Trim());
                                            result_row["Quantity"] = quantity;
                                            result_row["PType"] = row["PType"].ToString();
                                            StructureTable.Rows.Add(result_row);
                                        }
                                    }
                                    else
                                    {
                                        // Part is M type
                                        bool have_supplier = false;

                                        // Check for Supplier
                                        cmd_sql = myConnection.CreateCommand();
                                        cmd_sql.CommandText = "SELECT SupplierID FROM [VPartUnion] WHERE PartNo='" + row["PartNo"] + "'";
                                        cmd_sql.CommandTimeout = 30000;
                                        dataReader = cmd_sql.ExecuteReader();
                                        if (dataReader.Read())
                                        {
                                            if (!string.IsNullOrEmpty(dataReader["SupplierID"].ToString().Trim()))
                                            {
                                                have_supplier = true;                                                
                                            }                                            
                                        }
                                        dataReader.Close();
                                        cmd_sql.Dispose();

                                        if (have_supplier)
                                        {                                            
                                            // Have Supplier
                                            DataRow result_row = StructureTable.NewRow();
                                            result_row["RobotNo"] = robotno;
                                            result_row["PartNo"] = row["PartNo"].ToString().Trim();
                                            float quantity = 0;
                                            quantity = float.Parse(row["Quantity"].ToString().Trim());
                                            result_row["Quantity"] = quantity;
                                            result_row["PType"] = row["PType"].ToString();
                                            StructureTable.Rows.Add(result_row);
                                        }
                                        else
                                        {
                                            // Not have supplier                                            
                                            cmd_sql = myConnection.CreateCommand();
                                            cmd_sql.CommandText = "SELECT COMPID, PARTNO,"
                                                + " QUANTITY*" + row["Quantity"].ToString().Trim() + " AS QUANTITY, PARTTYPE"
                                                + " FROM [241_KoseiUnion]"
                                                + " WHERE COMPID='" + row["PartNo"].ToString() + "'";
                                            cmd_sql.CommandTimeout = 30000;
                                            dataReader = cmd_sql.ExecuteReader();
                                            int l = 0;
                                            while (dataReader.Read())
                                            {
                                                if (dataReader["COMPID"] != DBNull.Value)
                                                {
                                                    l++;
                                                    DataRow new_row = nextTable.NewRow();
                                                    new_row["RobotNo"] = dataReader["COMPID"].ToString().Trim();
                                                    new_row["PartNo"] = dataReader["PARTNO"].ToString().Trim();
                                                    float quantity = 0;
                                                    quantity = float.Parse(dataReader["QUANTITY"].ToString().Trim());
                                                    new_row["Quantity"] = quantity;
                                                    new_row["PType"] = dataReader["PARTTYPE"].ToString();
                                                    nextTable.Rows.Add(new_row);
                                                }
                                            }
                                            dataReader.Close();
                                            cmd_sql.Dispose();
                                            if (l == 0)
                                            {
                                                DataRow result_row = StructureTable.NewRow();
                                                result_row["RobotNo"] = robotno;
                                                result_row["PartNo"] = row["PartNo"].ToString().Trim();
                                                float quantity = 0;
                                                quantity = float.Parse(row["Quantity"].ToString().Trim());
                                                result_row["Quantity"] = quantity;
                                                result_row["PType"] = row["PType"].ToString();
                                                StructureTable.Rows.Add(result_row);
                                            }
                                        }
                                    }
                                }
                            }
                            catch (Exception ex)
                            {
                                /*
                                if (masterConnection.State == ConnectionState.Open)
                                {
                                    masterConnection.Close();
                                }
                                MessageBox.Show("Cannot calculate: " + ex.Message); */
                                MessageBox.Show("Cannot calculate: " + ex.Message);
                            }
                        }
                        currentTable.Rows.Clear();
                        currentTable = nextTable.Copy();
                        nextTable.Rows.Clear();
                    }
                    //cmd_sql.Dispose(); 
                    //masterConnection.Close();
                    myConnection.Close();

                    foreach (DataRow result_row in StructureTable.Rows)
                    {
                        if (result_row["PType"].ToString() != "F")
                        {
                            DataRow[] add_row = resultTable.Select("RobotNo='" + result_row["RobotNo"].ToString() + "'"
                                + " AND PartNo='" + result_row["PartNo"] + "'");
                            if (add_row.Length > 0)
                            {
                                foreach (DataRow r in add_row)
                                {
                                    r.BeginEdit();
                                    r["Quantity"] = (float)r["Quantity"] + (float)result_row["Quantity"];
                                    r.EndEdit();
                                }
                            }
                            else
                            {
                                DataRow n = resultTable.NewRow();
                                n["RobotNo"] = result_row["RobotNo"].ToString();
                                n["PartNo"] = result_row["PartNo"].ToString();
                                n["Quantity"] = (float)result_row["Quantity"];
                                n["PType"] = result_row["PType"].ToString();
                                resultTable.Rows.Add(n);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {                   
                    /*
                    if (masterConnection.State == ConnectionState.Open)
                    {
                        masterConnection.Close();
                    }
                    //MessageBox.Show("Cannot get Robot list"); */
                    MessageBox.Show("Cannot get structure 1: " + ex.Message); 
                }
            }
            
            return resultTable;
        }
        

        private void buttonBack_Click(object sender, EventArgs e)
        {
            this.state = 0;
            this.labelControl1.Text = "Master Plan";

            this.gridMasterPlan.BringToFront();
            
            this.buttonBack.Visible = false;
            this.buttonExport.Visible = false;
            this.buttonDelayOrder.Visible = false;
            this.buttonFinalOrder.Visible = false;
            this.buttonYoyaku.Visible = false;

            this.buttonViewLast.Visible = true;
            this.buttonRefresh.Visible = true;
            this.buttonImport.Visible = true;
            this.buttonDelete.Visible = true;
            this.buttonForecast.Visible = true;
            this.getMType.Visible = true;            
            
            LoadData();
        }

        private void buttonExport_Click(object sender, EventArgs e)
        {
            Cursor currentCursor;
            switch (this.state)
            {                
                case 1:
                    currentCursor = Cursor.Current;
                    Cursor.Current = Cursors.WaitCursor;
                    try
                    {
                        SaveFileDialog saveFDlg = new SaveFileDialog();
                        saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                        saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                        saveFDlg.Title = "Save As";
                        //saveFDlg.CheckFileExists = true;
                        saveFDlg.FileName = "FinalOrder.xlsx";
                        if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                        {
                            if (!string.IsNullOrEmpty(saveFDlg.FileName))
                            {
                                FinalOrder.OptionsPrint.PrintDetails = true;
                                FinalOrder.OptionsPrint.ExpandAllDetails = true;
                                FinalOrder.OptionsPrint.AutoWidth = true;
                                FinalOrder.ExportToXlsx(saveFDlg.FileName);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Warning!");
                    }
                    finally
                    {
                        Cursor.Current = currentCursor;
                    }
                    break;
                case 2:
                    currentCursor = Cursor.Current;
                    Cursor.Current = Cursors.WaitCursor;
                    try
                    {
                        SaveFileDialog saveFDlg = new SaveFileDialog();
                        saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                        saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                        saveFDlg.Title = "Save As";
                        //saveFDlg.CheckFileExists = true;
                        saveFDlg.FileName = "CancelOrDelay.xlsx";
                        if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                        {
                            if (!string.IsNullOrEmpty(saveFDlg.FileName))
                            {
                                CancelOrDelay.OptionsPrint.PrintDetails = true;
                                CancelOrDelay.OptionsPrint.ExpandAllDetails = true;
                                CancelOrDelay.OptionsPrint.AutoWidth = true;
                                CancelOrDelay.ExportToXlsx(saveFDlg.FileName);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Warning!");
                    }
                    finally
                    {
                        Cursor.Current = currentCursor;
                    }
                    break;

                case 3:
                    currentCursor = Cursor.Current;
                    Cursor.Current = Cursors.WaitCursor;
                    try
                    {
                        SaveFileDialog saveFDlg = new SaveFileDialog();
                        saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                        saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                        saveFDlg.Title = "Save As";
                        //saveFDlg.CheckFileExists = true;
                        saveFDlg.FileName = "Yoyaku.xlsx";
                        if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                        {
                            if (!string.IsNullOrEmpty(saveFDlg.FileName))
                            {
                                Yoyaku.OptionsPrint.PrintDetails = true;
                                Yoyaku.OptionsPrint.ExpandAllDetails = true;
                                Yoyaku.OptionsPrint.AutoWidth = true;
                                Yoyaku.ExportToXlsx(saveFDlg.FileName);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Warning!");
                    }
                    finally
                    {
                        Cursor.Current = currentCursor;
                    }
                    break;
            }
        }

        private void buttonViewLast_Click(object sender, EventArgs e)
        {
            ShowFinalOrder();
        }

        private void buttonFinalOrder_Click(object sender, EventArgs e)
        {
            ShowFinalOrder();
        }

        private void buttonDelayOrder_Click(object sender, EventArgs e)
        {
            ShowDelayOrder();
        }

        //////////////////////////////////////////////
        /// Load Kosei Union to local database
        //////////////////////////////////////////////
        private void loadKoseiUnionAS400()
        {
            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
            ModuleWaitDialog.SetWaitDialogCaption("Updateing Kosei table...");
            try
            {
                myConnection.Open();
                SqlCommand sql = myConnection.CreateCommand();
                sql.CommandText = "DELETE [241_KoseiUnion]";
                sql.CommandTimeout = 30000;
                sql.ExecuteNonQuery();
                sql.Dispose();

                sql = myConnection.CreateCommand();
                sql.CommandText = "INSERT INTO [241_KoseiUnion] ([COMPID], [PARTNO], [QUANTITY], [PARTTYPE], [NOTE])"
                    + " SELECT [COMPID], [PARTNO], [QUANTITY], [PARTTYPE], [NOTE] FROM [V241Kosei]";
                sql.CommandTimeout = 30000;
                sql.ExecuteNonQuery();
                sql.Dispose();

                sql = myConnection.CreateCommand();
                sql.CommandText = "DELETE [222_PartAS400]";
                sql.CommandTimeout = 30000;
                sql.ExecuteNonQuery();
                sql.Dispose();

                sql = myConnection.CreateCommand();
                sql.CommandText = "INSERT INTO [222_PartAS400] ([PartNo], [PartNoPurchase], [PartName], [PartType], [MakerID],"
                    + " [SupplierID], [LeadTime], [MOQ], [Marume], [UnitID], [IQUnit], [UnitExchange])"
                    + " SELECT [PARTNO], [PARTNOPURCHASE], [PARTNAME], [PARTTYPE], [MAKERID], [SUPPLIERID], [LEADTIME],"
                    + " [MOQ], [MARUME], [UNITID], [IQUNIT], [EXCHANGEUNIT] FROM [VPart]";
                sql.CommandTimeout = 30000;
                sql.ExecuteNonQuery();
                sql.Dispose();
                myConnection.Close();
                //loadKoseiUnion = true;
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
            }
            ModuleWaitDialog.CloseWaitDialog();
        }

        private void buttonYoyaku_Click(object sender, EventArgs e)
        {
            ShowYoyaku();
        }
    }
}
